Database Info & Basics

https://tryhackme.com/room/sqlinjectionlm

Relational Vs Non-Relational Databases:
A relational database stores information in tables, and often, the tables share information between them; they use columns to specify and define the data being stored and rows actually to store the data. The tables will often contain a column that has a unique ID (primary key), which will then be used in other tables to reference it and cause a relationship between the tables, hence the name relational database.

Non-relational databases, sometimes called NoSQL, on the other hand, are any sort of database that doesn't use tables, columns and rows to store the data. A specific database layout doesn't need to be constructed so each row of data can contain different information, giving more flexibility over a relational database.  Some popular databases of this type are MongoDB, Cassandra and ElasticSearch.

Spotting SQLi in Code

Value $name is taken directly from the POST request the client makes while the p-hash is the hash computed on the password.

Notice how a single ' in the $name value now, this creates a problem for most versions of SQL (in this case sqlite). Using ' OR 1=1 -- as an entry would cancel out the server-side password hash as all the rules match this condition.

$name = ' OR 1=1; --'

This causes it to run SELECT name from users where name= and spits out

Basic SQL queries

Limit results to 1

Select * from USERS LIMIT 1;

Check for users not equal to admin

select * from users where username != 'admin';

Look for data that isn't an exact match

select * from users where username like 'a%';

UNION statement combining with the results of 2 SELECT statements into 1 set

SELECT name,address,city,postcode from customers UNION SELECT company,address,city,postcode from suppliers;

INSERT statement

insert into users (username,password) values ('bob','password123');

UPDATE statement

update users SET username='root',password='pass123' where username='admin';

DELETE statement

SELECT name,address,city,postcode from customers UNION SELECT company,address,city,postcode from suppliers;

What does an SQLi vuln look like?

Take the following scenario where you've come across an online blog, and each blog entry has a unique ID number. The blog entries may be either set to public or private, depending on whether they're ready for public release. The URL for each blog entry may look something like this:
https://website.thm/blog?id=1

From the URL above, you can see that the blog entry selected comes from the id parameter in the query string. The web application needs to retrieve the article from the database and may use an SQL statement that looks something like the following:
SELECT * from blog where id=1 and private=0 LIMIT 1;

Let's pretend article ID 2 is still locked as private, so it cannot be viewed on the website. We could now instead call the URL:
https://website.thm/blog?id=2;--

Which would then, in turn, produce the SQL statement:
SELECT * from blog where id=2;-- and private=0 LIMIT 1;

The semicolon in the URL signifies the end of the SQL statement, and the two dashes cause everything afterwards to be treated as a comment. By doing this, you're just, in fact, running the query:
SELECT * from blog where id=2;--

Interpreters

Interpreters are basically ELF binaries, that process text into actual code. Basically the application prepares text to be processed by the interpreter. The text is fed to the interpreter by an application (usually developed bespoke which is where all the input field vulns actually come in to play).
1. Read some text
2. Split the text into tokens
3. Tokens are combined into Abstract Syntax Trees (ASTs)
4. Transform ASTs into different ASTs for different reasons
5. Execute ASTs

SQLi Types

In-Band SQL Injection
In-Band SQL Injection is the easiest type to detect and exploit; In-Band just refers to the same method of communication being used to exploit the vulnerability and also receive the results, for example, discovering an SQL Injection vulnerability on a website page and then being able to extract data from the database to the same page.

Error-Based SQL Injection
This type of SQL Injection is the most useful for easily obtaining information about the database structure, as error messages from the database are printed directly to the browser screen. This can often be used to enumerate a whole database. 

Union-Based SQL Injection
This type of Injection utilises the SQL UNION operator alongside a SELECT statement to return additional results to the page. This method is the most common way of extracting large amounts of data via an SQL Injection vulnerability.

Some Useful Methods

Note this is being used with in-band SQLi

group_concat() gets the specified column (in our case, table_name) from multiple returned rows and puts it into one string separated by commas.
information_schema database; every user of the database has access to this, and it contains information about all the databases and tables the user has access to

0 UNION SELECT 1,2,group_concat(table_name) FROM information_schema.tables WHERE table_schema = 'sqli_one'

Because 1=1 is a true statement and we've used an OR operator, this will always cause the query to return as true, which satisfies the web applications logic that the database found a valid username/password combination and that access should be allowed.

' OR 1=1;--

Connection

Interact with postgresql

psql -h <remote_host> -U <username> -d <database>

SQLi

In-band SQL Injection

This technique is considered the most common and straightforward type of SQL injection attack. In this technique, the attacker uses the same communication channel for both the injection and the retrieval of data. There are two primary types of in-band SQL injection:

• Error-Based SQL Injection: The attacker manipulates the SQL query to produce error messages from the database. These error messages often contain information about the database structure, which can be used to exploit the database further. Example: SELECT * FROM users WHERE id = 1 AND 1=CONVERT(int, (SELECT @@version)). If the database version is returned in the error message, it reveals information about the database.
• Union-Based SQL Injection: The attacker uses the UNION SQL operator to combine the results of two or more SELECT statements into a single result, thereby retrieving data from other tables. Example: SELECT name, email FROM users WHERE id = 1 UNION ALL SELECT username, password FROM admin.

Inferential (Blind) SQL Injection

Inferential SQL injection does not transfer data directly through the web application, making exploiting it more challenging. Instead, the attacker sends payloads and observes the application’s behaviour and response times to infer information about the database. There are two primary types of inferential SQL injection:

• Boolean-Based Blind SQL Injection: The attacker sends an SQL query to the database, forcing the application to return a different result based on a true or false condition. By analysing the application’s response, the attacker can infer whether the payload was true or false. Example: SELECT * FROM users WHERE id = 1 AND 1=1 (true condition) versus SELECT * FROM users WHERE id = 1 AND 1=2 (false condition). The attacker can infer the result if the page content or behaviour changes based on the condition.
• Time-Based Blind SQL Injection: The attacker sends an SQL query to the database, which delays the response for a specified time if the condition is true. By measuring the response time, the attacker can infer whether the condition is true or false. For example, SELECT * FROM users WHERE id = 1; IF (1=1) WAITFOR DELAY '00:00:05'--. If the response is delayed by 5 seconds, the attacker can infer that the condition was true.

MySQL Information & Execution Context Functions

File & command exec indicators

App-specific high-value tables